#! /bin/bash
export LANG=zh_CN.UTF-8
PRESTO_HOME=/export/server/presto/bin/presto


${PRESTO_HOME} --catalog hive --server 192.168.88.80:8090 --execute "

delete from hive.edu_dws.hkl_dws_check_wide_timecnt where 1=1;
insert into hive.edu_dws.hkl_dws_check_wide_timecnt
with tmp1 as (
select
    class_date,
--打卡时间
    signin_date as dt,
    date_parse(substring (signin_time,1,19),'%Y-%m-%d %H:%i:%s') as signin_time,
    class_id,
    student_id,
    begin_time,
    begin_time_type,
    end_time,
    end_time_type,
    days,
    morning_begin_time,
    morning_end_time,
    afternoon_begin_time,
    afternoon_end_time,
    evening_begin_time,
    evening_end_time,
--上午打卡时间范围
    date_add('minute',-40,date_parse(concat(signin_date,' ',morning_begin_time),'%Y-%m-%d %H:%i:%s') )  as moring_begin_signin_time,     --上午打卡开始时间
    date_add('minute',10,date_parse(concat(signin_date,' ',morning_begin_time),'%Y-%m-%d %H:%i:%s') )  as moring_end_signin_time,       --上午打卡结束时间
--下午打卡时间范围
    date_add('minute',-40,date_parse(concat(signin_date,' ',afternoon_begin_time),'%Y-%m-%d %H:%i:%s') )  as afternoon_begin_signin_time,     --下午打卡开始时间
    date_add('minute',10,date_parse(concat(signin_date,' ',afternoon_begin_time),'%Y-%m-%d %H:%i:%s') )  as afternoon_end_signin_time,       --下午打卡结束时间
--晚上打卡时间范围
    date_add('minute',-40,date_parse(concat(signin_date,' ',evening_begin_time),'%Y-%m-%d %H:%i:%s') )  as evening_begin_signin_time,     --晚上打卡开始时间
    date_add('minute',10,date_parse(concat(signin_date,' ',evening_begin_time),'%Y-%m-%d %H:%i:%s') )  as evening_end_signin_time,       --晚上打卡结束时间
    date_parse(concat(signin_date,' ', morning_end_time),'%Y-%m-%d %H:%i:%s') as morning_end_datetime,
    date_parse(concat(signin_date,' ', afternoon_end_time),'%Y-%m-%d %H:%i:%s') as afternoon_end_datetime,
    date_parse(concat(signin_date,' ', evening_end_time),'%Y-%m-%d %H:%i:%s') as evening_end_datetime,
    use_end_date,
    use_begin_date,
    row_number()over(partition by class_id,student_id,begin_time,end_time,class_date) as leave_rn,
    case when begin_time_type=1 then 'am'
         when begin_time_type=2 then 'pm'
         end as leave_type
from hive.edu_dwb.hkl_dwb_check_detail_wide
),
tmp2 as (select
    class_date,
    dt,
    signin_time,
    class_id,
    student_id,
    begin_time,
    begin_time_type,
    end_time,
    end_time_type,
    days,
    morning_begin_time,
    morning_end_time,
    afternoon_begin_time,
    afternoon_end_time,
    evening_begin_time,
    evening_end_time,
    moring_begin_signin_time,
    moring_end_signin_time,
    afternoon_begin_signin_time,
    afternoon_end_signin_time,
    evening_begin_signin_time,
    evening_end_signin_time,
    morning_end_datetime,
    afternoon_end_datetime,
    evening_end_datetime,
    use_end_date,
    use_begin_date,
-- 打卡标记
    case
         when signin_time between moring_begin_signin_time and morning_end_datetime then 'am' --上午打卡
         when signin_time between afternoon_begin_signin_time and afternoon_end_datetime  then 'pm' --下午打卡
         when signin_time between evening_begin_signin_time and evening_end_datetime   then 'nt' --晚上打卡
         else 'other' end as signin_time_type
from tmp1
),
tmp3 as (select
    class_date,
    dt,
    signin_time,
    class_id,
    student_id,
    begin_time,
    begin_time_type,
    end_time,
    end_time_type,
    days,
    morning_begin_time,
    morning_end_time,
    afternoon_begin_time,
    afternoon_end_time,
    evening_begin_time,
    evening_end_time,
    moring_begin_signin_time,
    moring_end_signin_time,
    afternoon_begin_signin_time,
    afternoon_end_signin_time,
    evening_begin_signin_time,
    evening_end_signin_time,
    morning_end_datetime,
    afternoon_end_datetime,
    evening_end_datetime,
    use_end_date,
    use_begin_date,
    signin_time_type,
    row_number()over(partition by class_id,student_id,dt,signin_time_type order by signin_time) as signin_rn

from tmp2
where  signin_time_type<>'other'
),
tmp4 as (select
*,
case
    when signin_time between moring_begin_signin_time and moring_end_signin_time and signin_time_type='am' then 'moring_signin'
    when signin_time between moring_end_signin_time and morning_end_datetime and signin_time_type='am' then 'moring_last_signin'
    when signin_time between afternoon_begin_signin_time and afternoon_end_signin_time and signin_time_type='pm' then 'afternoon_signin'
    when signin_time between afternoon_end_signin_time and afternoon_end_datetime and signin_time_type='pm' then 'afternoon_last_signin'
    when signin_time between evening_begin_signin_time and evening_end_signin_time and signin_time_type='nt' then 'evening_signin'
    when signin_time between evening_end_signin_time and evening_end_datetime and signin_time_type='nt' then 'evening_last_signin'
else 'other' end as signin_type
from tmp3
where signin_rn=1
),

studying_cnt as (select
dt,
class_id,
signin_time_type,
count(if(signin_type in ('moring_signin','afternoon_signin','evening_signin') ,student_id,null)) as studying_cnt,
count(if(signin_type in ('moring_last_signin','afternoon_last_signin','evening_last_signin') ,student_id,null)) as late_cnt
from tmp4
group by dt,class_id,signin_time_type
),

leave_tmp as (select
substring (begin_time,1,10) as dt,
class_id,
count(student_id) as leave_cnt,
leave_type
from tmp1
where begin_time is not null and leave_rn=1
group by substring (begin_time,1,10), class_id ,leave_type
),

cnt as (
select
'2024-08-24' as create_date,
dt,
class_id,
signin_time_type as time_type,
studying_cnt,
late_cnt,
0 as leave_cnt
from studying_cnt
union all
select
'2024-08-24' as create_date,
dt,
class_id,
leave_type as time_type,
0 as studying_cnt,
0 as late_cnt,
leave_cnt
from leave_tmp
)
select * from cnt;"




















